﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SN.Services
{
    public class Extfunction
    {
        public string GetFe含量_Sql(string sCh, string sDay, DateTime CurDate)
        {
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string Sql = string.Format("select isnull(Scsj_Clh_Fe,0) from dbo.p_Scsj where Scsj_Ch = '{0}' and  CONVERT(varchar(10), Scsj_Rq, 120) = '{1}'", sCh, sDate);
            return Sql;
        }
        public string GetSi含量_Sql(string sCh, string sDay, DateTime CurDate)
        {
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string Sql = string.Format("select isnull(Scsj_Clh_Si,0) from dbo.p_Scsj where Scsj_Ch = '{0}' and  CONVERT(varchar(10), Scsj_Rq, 120) = '{1}'", sCh, sDate);
            return Sql;
        }
        //原铝质量 
        public string Get原铝品位_Sql(string sCh, string sDay, DateTime CurDate)
        {
            //原铝品位(槽号,天数)
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string Sql = string.Format("select 'Al' + convert(varchar(10), isnull(Scsj_Clh_Ylzl,0)) from dbo.p_Scsj where Scsj_Ch = '{0}' and  CONVERT(varchar(10), Scsj_Rq, 120) = '{1}'", sCh, sDate);
            return Sql;
        }
        public string Get出铝量_Sql(string sGq, string sDay, DateTime CurDate)
        {
            //  lbfunList.Items.Add("出铝量(工区,天数)");
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string sAddWhere = "";
            if (sGq == "11")
                sAddWhere = "Scsj_Ch >= '1001' and Scsj_Ch <= '1056'";
            else if (sGq == "12")
                sAddWhere = "Scsj_Ch >= '1057' and Scsj_Ch <= '1112'";
            else if (sGq == "13")
                sAddWhere = "Scsj_Ch >= '1113' and Scsj_Ch <= '1168'";
            else if (sGq == "21")
                sAddWhere = "Scsj_Ch >= '2001' and Scsj_Ch <= '2056'";
            else if (sGq == "22")
                sAddWhere = "Scsj_Ch >= '2057' and Scsj_Ch <= '2112'";
            else if (sGq == "23")
                sAddWhere = "Scsj_Ch >= '2113' and Scsj_Ch <= '2168'";
            return string.Format("select sum(Scsj_Clh_Cll) from dbo.p_Scsj where  CONVERT(varchar(10), Scsj_Rq, 120) = '{0}' and  {1}", sDate, sAddWhere);
        }
        public string Get日平均电压_Sql(string sGq, string sDay, DateTime CurDate)
        {
            //日平均电压(工区,天数)
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string sAddWhere = "";
            if (sGq == "11")
                sAddWhere = "Scsj_Ch >= '1001' and Scsj_Ch <= '1056'";
            else if (sGq == "12")
                sAddWhere = "Scsj_Ch >= '1057' and Scsj_Ch <= '1112'";
            else if (sGq == "13")
                sAddWhere = "Scsj_Ch >= '1113' and Scsj_Ch <= '1168'";
            else if (sGq == "21")
                sAddWhere = "Scsj_Ch >= '2001' and Scsj_Ch <= '2056'";
            else if (sGq == "22")
                sAddWhere = "Scsj_Ch >= '2057' and Scsj_Ch <= '2112'";
            else if (sGq == "23")
                sAddWhere = "Scsj_Ch >= '2113' and Scsj_Ch <= '2168'";
            //去工作电压
            return string.Format("select Convert(decimal(13,2),avg(Scsj_Cy_Pjdy)) from dbo.p_Scsj where  CONVERT(varchar(10), Scsj_Rq, 120) = '{0}' and  {1}", sDate, sAddWhere);
        }
        public string Get月平均电压_Sql(string sGq, string sMonth, DateTime CurDate)
        {
            //月平均电压(工区,月份)
            string sDate = CurDate.Year.ToString() + "-" + Convert.ToInt16(sMonth).ToString("00");
            string sAddWhere = "";
            if (sGq == "11")
                sAddWhere = "Scsj_Ch >= '1001' and Scsj_Ch <= '1056'";
            else if (sGq == "12")
                sAddWhere = "Scsj_Ch >= '1057' and Scsj_Ch <= '1112'";
            else if (sGq == "13")
                sAddWhere = "Scsj_Ch >= '1113' and Scsj_Ch <= '1168'";
            else if (sGq == "21")
                sAddWhere = "Scsj_Ch >= '2001' and Scsj_Ch <= '2056'";
            else if (sGq == "22")
                sAddWhere = "Scsj_Ch >= '2057' and Scsj_Ch <= '2112'";
            else if (sGq == "23")
                sAddWhere = "Scsj_Ch >= '2113' and Scsj_Ch <= '2168'";
            //取月平均电压
            return string.Format("select Convert(decimal(13,2),avg(Scsj_Cy_Pjdy)) from dbo.p_Scsj where  CONVERT(varchar(7),scsj_rq,120) = '{0}' and  {1}", sDate, sAddWhere);
        }

        //氧化铝浓度
        public string Get日平均氧化铝浓度_Sql(string sGq, string sDay, DateTime CurDate)
        {
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string sAddWhere = "";
            if (sGq == "11")
                sAddWhere = "Scsj_Ch >= '1001' and Scsj_Ch <= '1056'";
            else if (sGq == "12")
                sAddWhere = "Scsj_Ch >= '1057' and Scsj_Ch <= '1112'";
            else if (sGq == "13")
                sAddWhere = "Scsj_Ch >= '1113' and Scsj_Ch <= '1168'";
            else if (sGq == "21")
                sAddWhere = "Scsj_Ch >= '2001' and Scsj_Ch <= '2056'";
            else if (sGq == "22")
                sAddWhere = "Scsj_Ch >= '2057' and Scsj_Ch <= '2112'";
            else if (sGq == "23")
                sAddWhere = "Scsj_Ch >= '2113' and Scsj_Ch <= '2168'";

            return string.Format("select Convert(decimal(13,2),avg(Scsj_Clh_Yhlnd)) from dbo.p_Scsj where  CONVERT(varchar(10), Scsj_Rq, 120) = '{0}' and  {1}", sDate, sAddWhere);
        }
        public string Get超35以上氧化铝浓度_Sql(string sGq, string sDay, DateTime CurDate)
        {
            string sDate = CurDate.Year.ToString() + "-" + CurDate.Month.ToString("00") + "-" + Convert.ToInt16(sDay).ToString("00");
            string sAddWhere = "";
            if (sGq == "11")
                sAddWhere = "Scsj_Ch >= '1001' and Scsj_Ch <= '1056'";
            else if (sGq == "12")
                sAddWhere = "Scsj_Ch >= '1057' and Scsj_Ch <= '1112'";
            else if (sGq == "13")
                sAddWhere = "Scsj_Ch >= '1113' and Scsj_Ch <= '1168'";
            else if (sGq == "21")
                sAddWhere = "Scsj_Ch >= '2001' and Scsj_Ch <= '2056'";
            else if (sGq == "22")
                sAddWhere = "Scsj_Ch >= '2057' and Scsj_Ch <= '2112'";
            else if (sGq == "23")
                sAddWhere = "Scsj_Ch >= '2113' and Scsj_Ch <= '2168'";

            return string.Format("select count(1) from dbo.p_Scsj where  CONVERT(varchar(10), Scsj_Rq, 120) = '{0}' and Scsj_Clh_Yhlnd >3.5 and  {1}", sDate, sAddWhere);
        }
    }
}
